
package com.vanda.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Vector;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.Session;
import net.sf.hibernate.dialect.Dialect;
import net.sf.hibernate.engine.SessionImplementor;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.vanda.data.AbstractSelectData;
import com.vanda.data.BeanListBase;
import com.vanda.db.cfg.MappingInfo;
import com.vanda.db.exception.CreateException;
import com.vanda.db.exception.DbException;
import com.vanda.db.util.BeanUtils;
import com.vanda.db.util.DBUtils;
import com.vanda.db.util.XQLParser;
import com.vanda.util.LogFunc;
/**
 * SQL Accessor访问类 <br>
 * @author 王瑛<br>
 * @version Version 1.00<br>
 */
public class SQLAccessor extends Accessor {
	/** 取得日志类 */
	private static Log log = LogFactory.getLog(SQLAccessor.class);
	
	/** 构造方法 */
	SQLAccessor(Session session) {
		super(session);
	}
	/**
	 * 使用SQL查询，不能作翻页查询.<p>
	 * 说明：
	 * 查询字段应该与dataBean属性名称相匹配(大小写不敏感)，这样查询结果才能设置到相应的属性。
	 * 例如：
	 * sql = "select a.*,b.name from a,b";(a表有a1,a2字段)
	 * DataBean类有a1,a2,b1三个属性
	 * 那么查询结果就只有a1,a2有值，可以将sql修改成"select a.*,b.name as b1 from a,b".
	 * 还有一点注意,数据库类型应可以转换对应Java类型.
	 * @param sql 完整的查询语句
	 * @return 查询结果List,list的元素类型是dataBean类型
	 * @throws DbException
	 */
	public BeanListBase query(String sql, List lstValue, Class returnClass)
		throws DbException {
		AbstractSelectData selectData = new AbstractSelectData();
		selectData.setReturnClass(returnClass);
		return query(sql, null, lstValue, selectData);
	}
	
	/**
	 * 翻页查询，查询总数的sql由默认规则生成
	 * @param sql 完整查询语句
	 * @param lstValue 查询参数集合
	 * @param selectData 翻页设置,必须设置pageNo，returnClass属性
	 * @return 
	 * @throws DbException
	 */
	private BeanListBase query(
		String sql,
		List lstValue,
		AbstractSelectData selectData)
		throws DbException {
		String countSQL = XQLParser.getCountXQL(sql);
		return query(sql, countSQL, lstValue, selectData);
	}
	
	/**
	 * 翻页查询，用户指定查询总数sql.<p>
	 * 说明：
	 * 查询字段应该与dataBean属性名称相匹配(大小写不敏感)，这样查询结果才能设置到相应的属性。
	 * 例如：
	 * sql = "select a.*,b.name from a,b";(a表有a1,a2字段)
	 * DataBean类有a1,a2,b1三个属性
	 * 那么查询结果就只有a1,a2有值，可以将sql修改成"select a.*,b.name as b1 from a,b".
	 * 还有一点注意,数据库类型应可以转换对应Java类型.
	 * @param sql 完整的查询语句
	 * @param countSQL 查询sql总记录数的sql
	 * @param lstValue 查询参数集合
	 * @param selectData 翻页设置,必须设置pageNo，returnClass属性
	 * @return 查询结果List,list的元素类型是dataBean类型
	 * @throws DbException
	 */
	private BeanListBase query(
		String sql,
		String countSQL,
		List lstValue,
		AbstractSelectData selectData)
		throws DbException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sErrMsg = null;
		Vector vtResult = null;
		try {
			//LogFunc.info("SQL : " + sql);
			boolean bPageQuery = selectData.getPageNo() > 0;
			int first = -1;
			int maxResult = -1;
			Connection conn = session.connection();
			if (bPageQuery) {
				//查询起点位置
				first =
					(selectData.getPageNo() - 1) * (selectData.getPageRowNum());
				//查询最大数量
				maxResult = selectData.getPageRowNum();
				if (countSQL != null) {
					LogFunc.info("CountSQL : " + countSQL);
					String result = getResult(conn, countSQL,lstValue);
					selectData.setRecordCount(Integer.parseInt(result));
				}
			}
			
			//modify by eagle 20070806,因hibernate配置文件已改用vanda包下的方言类，因此不需指定
			//Dialect dialect = DialectFactory.getDialect(super.session);
			Dialect dialect =
				((SessionImplementor) (super.session))
					.getFactory()
					.getDialect();
			
			//是否有限制结果集或不支持查询
			boolean useLimit = dialect.supportsLimit() && (bPageQuery);
			//是否从第一行开始查询，true-不从第一行，false-从第一行
			boolean hasFirstRow = first > 0;
			boolean useOffset =
				hasFirstRow && useLimit && dialect.supportsLimitOffset();
			//是否需要滚动查询
			boolean scrollable =
				((SessionImplementor) (super.session))
					.getFactory()
					.isScrollableResultSetsEnabled()
					&& ((hasFirstRow && !useOffset));
			if (useLimit) {
				sql = dialect.getLimitString(sql.trim(), useOffset);
				LogFunc.info("Limit SQL : " + sql);
				//如果参数在前面
				bindLimitParameters(
					lstValue,
					dialect.bindLimitParametersFirst(),
					first,
					maxResult,
					dialect);
			}
			HashMap hm =
				BeanUtils.getFieldInfo(selectData.getReturnClass(), true);
			//如果不能限制结果集但可以使用滚动集，将ps设置成可滚动
			if (!useLimit && scrollable) {
				LogFunc.info("Use Scrollable");
				ps =
					conn.prepareStatement(
						sql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
			} else {
				LogFunc.info("Use Non Scrollable");
				ps = conn.prepareStatement(sql);
			}
			if (bPageQuery && !useLimit) {
				ps.setMaxRows(first + maxResult);
			}
			//设置属性值
			if (lstValue != null && lstValue.size() > 0) {
				Integer[] types =
					(Integer[]) DBUtils.convert(
						lstValue.toArray(),
						DBUtils.SQL_TYPE);
				for (int i = 0; i < types.length; i++) {
					ps.setObject(i + 1, lstValue.get(i), types[i].intValue());
				}
			}
			rs = ps.executeQuery();
			if (bPageQuery && !useLimit) {
				advance(rs, first, (SessionImplementor) session);
			}
			vtResult = new Vector();
			//返回sql语句的查询字段
			String[] sFields = DBUtils.getQueryField(rs);
			long index = 0;
			if (maxResult == -1) {
				maxResult = Integer.MAX_VALUE;
			}
			for (int count = 0; count < maxResult && rs.next(); count++) {
				Object rowRecord =
					DBUtils.getRowObject(
						selectData.getReturnClass(),
						rs,
						sFields,
						hm);
				vtResult.addElement(rowRecord);
			}
		} catch (DbException e) {
			throw e;
		} catch (Exception e) {
			throw new DbException("Query Error!", e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
			} catch (Exception ex) {
				throw new DbException("Close DB Source Error!", ex);
			}
		}
		BeanListBase beanList = new BeanListBase(vtResult);
		beanList.setSelectData(selectData);
		return beanList;
	}
	
	/**
	 * 返回查询结果第一条记录，如果无结果，则返回null
	 * @param sql
	 * @param dataBean
	 * @return
	 * @throws DbException
	 */
	private Object find(String sql, List lstValue, Class returnClass)
		throws DbException {
		List lstResult = query(sql, lstValue, returnClass).getReferenceObject();
		Object first = null;
		if (lstResult.size() > 0) {
			first = lstResult.get(0);
		}
		return first;
	}
	
	/**
	 * 执行SQL语句.
	 * @param sql 完整的SQL操作语句
	 * @param paramList 绑定参数
	 * @return int 执行INSERT, UPDATE, DELETE的行数
	 * @throws DbException
	 */
	public int execute(String sql, List paramList) throws DbException {
		Connection conn = null;
		PreparedStatement stmt = null;
		int row = 0;
		try {
			//LogFunc.info("SQL : " + sql);
			conn = session.connection();
			stmt = conn.prepareStatement(sql);
			//设置属性值
			if (paramList != null && paramList.size() > 0) {
				Integer[] types =
					(Integer[]) DBUtils.convert(
						paramList.toArray(),
						DBUtils.SQL_TYPE);
				for (int i = 0; i < types.length; i++) {
					stmt.setObject(
						i + 1,
						paramList.get(i),
						types[i].intValue());
				}
			}
			row = stmt.executeUpdate();
		} catch (HibernateException he) {
			he.printStackTrace();
			throw new DbException(he);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DbException(e);
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
			} catch (Exception ex) {
				throw new DbException(ex);
			}
		}
		return row;
	}
	
	/**
	 * 查询XML配置的sql,只返回查询结果第一条记录
	 * @param name xml配置文件对应的名称
	 * @param dataBean 查询参数，如果有多个，用Object[]传递，而不要用Collection接口类
	 * @return 返回查询结果第一条记录,如果没有查询结果,返回null
	 */
	public Object findName(String name, Object dataBean)
			throws DbException {
		BeanListBase beanList = queryName(name, dataBean);
		List resultList = beanList.getReferenceObject();
		if (resultList != null && resultList.size() > 0) {
			return resultList.get(0);
		}
		return null;	
	}
	
	/**
	 * 查询XML配置的sql
	 * @param name xml配置文件对应的名称
	 * @param dataBean 查询参数，如果有多个，用Object[]传递，而不要用Collection接口类
	 * @return
	 */
	public BeanListBase queryName(String name, Object dataBean)
		throws DbException {
		Object[] dataBeans = null;
		//如果是数组
		if (dataBean instanceof Object[]) {
			dataBeans = (Object[]) dataBean;
		} else {
			dataBeans = new Object[1];
			dataBeans[0] = dataBean;
		}
		return queryName(name, dataBeans);
	}
	
	/**
	 * 执行xml配置的sql(必须是非查询sql)
	 * @param name xml配置文件对应的名称
	 * @param dataBean 查询参数，如果有多个，用Object[]传递，而不要用Collection接口类
	 * @return int[],保存每个sql修改数据库的记录数
	 * @throws DbException
	 */
	public int[] executeName(String name, Object dataBean) throws DbException {
		Object[] dataBeans = null;
		//如果是数组
		if (dataBean instanceof Object[]) {
			dataBeans = (Object[]) dataBean;
		} else {
			dataBeans = new Object[1];
			dataBeans[0] = dataBean;
		}
		return executeName(name, dataBeans);
	}
	
	/**
	 * 将SQLAccessor适配成HQLAccessor，即共用一个session
	 * 在一个事务里想同时操作hql和sql时使用，最后关闭时只调用其中一个Accessor.close()方法
	 * @return
	 * @throws CreateException
	 */
	public HQLAccessor adapter() throws CreateException {
		if (super.adaptAccessor == null) {
			super.adaptAccessor =
				DBFactory.getAccessor(
					super.getSession(),
					DBFactory.ACCESSOR_TYPE_HQL);
			super.adaptAccessor.adaptAccessor = this;
		}

		return (HQLAccessor) super.adaptAccessor;
	}
	
	/**
	 * 查询XML配置的sql
	 * @param name xml配置文件对应的名称
	 * @param dataBean 查询参数
	 * @return
	 */
	private BeanListBase queryName(String name, Object[] dataBeans)
		throws DbException {
		BeanListBase lstResult = null;
		try {
			MappingInfo info = super.findMapping(name);
			XQLData[] datas = XQLParser.parse(info, dataBeans);
			Class returnCls = Class.forName(info.getReturnClass());
			XQLData countData = null;
			String countSQL = null;
			
			//是否翻页
			boolean bPage = false;
			AbstractSelectData selectData = null;
			if(	dataBeans[0] instanceof AbstractSelectData) {
				selectData = (AbstractSelectData)dataBeans[0];
				if(selectData.getPageNo() > 0){
					bPage = true;
				}
			}

			//如果是翻页，检查是否有Count HQL
			if (bPage == true) {
				countData = XQLParser.parseCountXQL(info, dataBeans);
				if(countData != null)
					countSQL = countData.getXql();
			}
			
			//翻页查询
			if (bPage) {
				selectData.setReturnClass(returnCls);
				if (countSQL != null && countSQL.trim().length() > 0) {
					lstResult =
						this.query(
							datas[0].getXql(),
							countSQL,
							datas[0].getValues(),
							(AbstractSelectData) dataBeans[0]);
				} else {
					lstResult =
						this.query(
							datas[0].getXql(),
							datas[0].getValues(),
							(AbstractSelectData) dataBeans[0]);
				}
			} else { //不做翻页查询
				lstResult =
					this.query(
						datas[0].getXql(),
						datas[0].getValues(),
						returnCls);
			}
		} catch (DbException e) {
			throw e;
		} catch (Exception e) {
			throw new DbException(e);
		}
		return lstResult;
	}
	
	/**
	 * 执行在xml中配置的SQL语句。
	 * @param name SQL配置的名称
	 * @param dataBean 用来传递参数的对象
	 * @return int 执行INSERT, UPDATE, DELETE的行数
	 * @throws DbException
	 */
	private int[] executeName(String name, Object[] dataBeans)
		throws DbException {
		int[] row = null;
		try {
			MappingInfo info = super.findMapping(name);
			XQLData[] datas = XQLParser.parse(info, dataBeans);
			row = new int[datas.length];
			for (int i = 0; i < datas.length; i++) {
				row[i] = this.execute(datas[i].getXql(), datas[i].getValues());
			}
		} catch (DbException e) {
			throw e;
		} catch (Exception e) {
			e.printStackTrace();
			throw new DbException("execute error!", e);
		}
		return row;
	}
	
	/**
	 * 取得查询sql的第一行的第一列记录
	 * @param conn 
	 * @param sql 查询sql
	 * @return 
	 * @throws Exception
	 */
	private String getResult(Connection conn, String sql ,List lstValue) throws Exception {
		String result = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sErrMsg = null;
		try {
			ps = conn.prepareStatement(sql);
			
			if(lstValue != null && lstValue.size() > 0){
				Integer[] types =
					(Integer[]) DBUtils.convert(
						lstValue.toArray(),
						DBUtils.SQL_TYPE);
				for (int i = 0; i < types.length; i++) {
					ps.setObject(i + 1, lstValue.get(i), types[i].intValue());
				}
			}
			
			rs = ps.executeQuery();
			if (rs.next()) {
				result = rs.getString(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (ps != null) {
					ps.close();
					ps = null;
				}
			} catch (Exception ex) {
				throw ex;
			}
		}
		return result;
	}
	
	/**
	 * 移动rs指针到开始位置
	 * @param rs
	 * @param firstRow 
	 * @param session
	 * @throws SQLException
	 */
	private void advance(
		final ResultSet rs,
		int firstRow,
		final SessionImplementor session)
		throws SQLException {
		//如果开始位置不为0，则移动指针
		if (firstRow != 0) {
			//如果db支持scroll
			if (session.getFactory().isScrollableResultSetsEnabled()) {
				LogFunc.info("we can go straight to the first required row ");
				rs.absolute(firstRow);
			} else {
				LogFunc.info(					"we need to step through the rows one row at a time (slow)");
				for (int m = 0; m < firstRow; m++)
					rs.next();
			}
		}
	}
	
	/**
	 * 根据db决定参数是max还是limit
	 * @param firstRow
	 * @param maxResult
	 * @param dialect
	 * @return
	 */
	private int getMaxOrLimit(int firstRow, int maxResult, Dialect dialect) {
		int lastRow = maxResult;
		if (dialect.useMaxForLimit()) {
			return lastRow + firstRow;
		} else {
			return lastRow;
		}
	}
	
	/**
	 * 绑定翻页参数
	 * @param lstValue
	 * @param first
	 * @param firstRow
	 * @param maxResult
	 * @param dialect
	 * @return
	 * @throws SQLException
	 */
	private int bindLimitParameters(
		List lstValue,
		boolean first,
		int firstRow,
		int maxResult,
		Dialect dialect)
		throws SQLException {
		if (!dialect.supportsVariableLimit())
			return 0;
		int lastRow = getMaxOrLimit(firstRow, maxResult, dialect);
		boolean hasFirstRow = firstRow > 0 && dialect.supportsLimitOffset();
		boolean reverse = dialect.bindLimitParametersInReverseOrder();
		//如果参数设置在前
		if (first == true) {
			if (hasFirstRow) {
				if (!reverse) {
					lstValue.add(0, new Integer(lastRow));
					lstValue.add(0, new Integer(firstRow));
				} else {
					lstValue.add(0, new Integer(firstRow));
					lstValue.add(0, new Integer(lastRow));
				}
			} else {
				lstValue.add(0, new Integer(lastRow));
			}
		} else {
			if (hasFirstRow) {
				if (!reverse) {
					lstValue.add(new Integer(firstRow));
					lstValue.add(new Integer(lastRow));
				} else {
					lstValue.add(new Integer(lastRow));
					lstValue.add(new Integer(firstRow));
				}
			} else {
				lstValue.add(new Integer(lastRow));
			}
		}
		return hasFirstRow ? 2 : 1;
	}
}
